<?php 
require_once($_SESSION["root_path"]."/general_queries.inc");

function query_applicants_with_exam_plan($exam_center_id=null, $room_number=null, $exam_id=null) {
	$q = query_not_excluded_applicants();
	$q->join_applicant("applicant_exam_room_beta");
	$q->where("applicant_exam_room_beta.room_number IS NOT NULL");
	$q->where("applicant_exam_room_beta.exam_id IS NOT NULL");
	if ($exam_center_id<>null)
		$q->where("applicant_exam_room_beta.exam_center_id=".$exam_center_id);
	if ($room_number<>null)
		$q->where("applicant_exam_room_beta.room_number='".$room_number."'");
	if ($exam_id<>null)
		$q->where("applicant_exam_room_beta.exam_id=".$exam_id);
	return $q;
}

function query_applicants_without_exam_plan($exam_center_id=null) {
	$q = query_not_excluded_applicants();
	$q->join_applicant("applicant_exam_room_beta");
	$q->where(Query::build_conditions(array("applicant_exam_room_beta.room_number IS NULL","applicant_exam_room_beta.exam_id IS NULL"),"OR"));
	if ($exam_center_id<>null)
		$q->where("applicant_exam_room_beta.exam_center_id=".$exam_center_id);
	return $q;
}

function query_exam_centers() {
	$q = new Query("exam_center");
	$q->where("exam_center.selection_campaign_id=".$_SESSION["campaign_id"]);
	return $q;
}

function query_exam_centers_having_rooms() {
	$q = query_exam_centers();
	$q->join("exam_center_rooms", null, array("exam_center_rooms.exam_center_id=exam_center.exam_center_id"));
	$q->where("exam_center_rooms.room IS NOT NULL");
	$q->select("DISTINCT exam_center.exam_center_id");
	return $q;
}

function query_exam_centers_having_no_room() {
	$q = query_exam_centers();
	$q->join("exam_center_rooms", null, array("exam_center_rooms.exam_center_id=exam_center.exam_center_id"));
	$q->where("exam_center_rooms.room IS NULL");
	$q->select("DISTINCT exam_center.exam_center_id");
	return $q;
}

function query_exam_centers_having_rooms_and_exam_plan() {
	$q = query_exam_centers();
	$q->join("exam_center_rooms", null, array("exam_center_rooms.exam_center_id=exam_center.exam_center_id"));
	$q->where("exam_center_rooms.room IS NOT NULL");
	$q->join("exam", null, array("exam.exam_center_id=exam_center.exam_center_id"));
	$q->where("exam.exam_id IS NOT NULL");
	$q->select("DISTINCT exam_center.exam_center_id");
	return $q;
}

function query_exam_rooms_needing_supervisor($exam_center_id=null) {
	$q = query_applicants_with_exam_plan();
	$q->join("supervisor", null, array("supervisor.exam_id=applicant_exam_room_beta.exam_id","supervisor.room=applicant_exam_room_beta.room_number"));
	$q->where("supervisor.supervisor_username IS NULL");
	if ($exam_center_id == null)
		$q->group("applicant_exam_room_beta.exam_center_id");
	else 
		$q->where("applicant_exam_room_beta.exam_center_id=".$exam_center_id);
	$q->group("applicant_exam_room_beta.room_number");
	return $q;
}
function query_exam_rooms_having_supervisor($exam_center_id=null) {
	$q = query_applicants_with_exam_plan();
	$q->join("supervisor", null, array("supervisor.exam_id=applicant_exam_room_beta.exam_id","supervisor.room=applicant_exam_room_beta.room_number"));
	$q->where("supervisor.supervisor_username IS NOT NULL");
	if ($exam_center_id == null)
		$q->group("applicant_exam_room_beta.exam_center_id");
	else 
		$q->where("applicant_exam_room_beta.exam_center_id=".$exam_center_id);
	$q->group("applicant_exam_room_beta.room_number");
	return $q;
}

function query_exam_centers_info($exam_center_id=null) {
	$q = new Query("exam_center");
	$q->join("province", null, array("province.province_id=exam_center.province_id"));
	$q->order("province.province_name");
	$q->order("exam_center.exam_center_name");
	$q->where("exam_center.selection_campaign_id=".$_SESSION["campaign_id"]);
	if ($exam_center_id <> null)
		$q->where("exam_center.exam_center_id=".$exam_center_id);
	return $q;
}

function query_exam_centers_info_if_applicants() {
	$q = query_exam_centers_info();
	$q->join("applicant_exam_room_beta", null, array("applicant_exam_room_beta.exam_center_id=exam_center.exam_center_id"));
	$q->join("applicant", null, array("applicant.applicant_id=applicant_exam_room_beta.applicant_id"));
	$q->where("applicant.excluded=0");
	$q->group("exam_center.exam_center_id");
	return $q;
}

function query_applicants_assigned_to_exam_center($exam_center_id) {
	$q = query_not_excluded_applicants();
	$q->join_applicant("applicant_exam_room_beta");
	$q->where("applicant_exam_room_beta.exam_center_id=".$exam_center_id);
	return $q;
}

function query_applicants_assigned_to_an_exam_room($exam_center_id) {
	$q = query_applicants_assigned_to_exam_center($exam_center_id);
	$q->where("applicant_exam_room_beta.room_number IS NOT NULL");
	return $q;
}
function query_applicants_not_assigned_to_an_exam_room($exam_center_id) {
	$q = query_applicants_assigned_to_exam_center($exam_center_id);
	$q->where("applicant_exam_room_beta.room_number IS NULL");
	return $q;
}

function query_exam_supervisors($exam_center_id, $room_number, $exam_id) {
	$q = new Query("supervisor");
	$q->join("staff", null, array("staff.username=supervisor.supervisor_username"));
	$q->where("supervisor.exam_id=".$exam_id);
	$q->where("supervisor.room='".$room_number."'");
	return $q;
}

?>